# 导入必要的模块
import os
import pandas as pd

# 定义文件路径 - 使用相对于脚本文件的路径
script_dir = os.path.dirname(os.path.abspath(__file__))
shi_qi = '半年报'
zi_chan_path = os.path.join(script_dir, '..','数据', shi_qi, '资产负债表.xlsx')
li_run_path = os.path.join(script_dir, '..', '数据', shi_qi, '利润表.xlsx')
result_path = os.path.join(script_dir, '..', '结果数据', '存货周转率(06-24).xlsx')


# 读取Excel文件中的存货数据
def read_inventory_data():
    try:
        df = pd.read_excel(zi_chan_path,engine='openpyxl', header = 2, index_col=0)
        df.columns = df.columns.strftime('%Y-%m-%d')
        # 时间按照从旧到新排序，然后除以一亿元,保留两位小数
        cun_huo = df.loc['存货(亿)'].reindex(df.loc['存货(亿)'].index[::-1])
        # 单位调整到亿元
        # cun_huo = cun_huo.apply(lambda x: round(x/100000000,2))
        cun_huo_mean = cun_huo.rolling(2).mean()
        cun_huo_mean = cun_huo_mean.drop('2006-06-30')
        return cun_huo_mean
    except Exception as e:
        print(f"读取文件时出错: {e}")
        return None

def read_cheng_ben():
    df = pd.read_excel(li_run_path, engine='openpyxl', header = 2, index_col=0)
    df.columns = df.columns.strftime('%Y-%m-%d')
    cheng_ben = df.loc['营业成本'].reindex(df.loc['营业成本'].index[::-1])
    # 单位调整到亿元
    cheng_ben = cheng_ben.apply(lambda x: round(x/100000000,2)).drop('2006-06-30')
    return cheng_ben


def read_jing_li_run_rate():
    item = '归属于母公司普通股股东的扣除非经常性损益的净利润'
    df = pd.read_excel(li_run_path, engine='openpyxl', header = 2, index_col=0)
    df.columns = df.columns.strftime('%Y-%m-%d')
    li_run = df.loc[item].reindex(df.loc[item].index[::-1])
 

    li_run = li_run.drop('2007-06-30')
    
    li_run = li_run.drop('2006-06-30')
    return li_run.pct_change().infer_objects(copy=False)

def read_xian_jin():
    df = pd.read_excel(zi_chan_path, engine='openpyxl', header = 2, index_col=0)
    df.columns = df.columns.strftime('%Y-%m-%d')
    xian_jin = df.loc['货币资金(亿)'].reindex(df.loc['货币资金(亿)'].index[::-1])
    # xian_jin = xian_jin.apply(lambda x: round(x/100000000,2))
    return xian_jin

def read_he_tong_fu_zhai():
    item = '合同负债(亿)'

    df = pd.read_excel(zi_chan_path, engine='openpyxl', header = 2, index_col=0)
    df.columns = df.columns.strftime('%Y-%m-%d')
    he_tong_fu_zhai = df.loc[item].reindex(df.loc[item].index[::-1])
    # 单位调整到亿元
    # he_tong_fu_zhai = he_tong_fu_zhai.apply(lambda x: round(x/100000000,2))
    return he_tong_fu_zhai




def zhou_zhuan_lv(cun_huo, cheng_ben):
    zhou_zhuan_lv =  cheng_ben / cun_huo
    return zhou_zhuan_lv.apply(lambda x: round(x,2))



# 执行读取操作
if __name__ == '__main__':
    cun_huo = read_inventory_data()
    cheng_ben = read_cheng_ben()
    zhou_zhuan_tian = zhou_zhuan_lv(cun_huo, cheng_ben)
    he_tong_fu_zhai = read_he_tong_fu_zhai()

    li_run_lv = read_jing_li_run_rate()
    xian_jin = read_xian_jin()
    df = pd.DataFrame({
        '存货': cun_huo,
        '货币资金': xian_jin,
        '成本': cheng_ben,
        '合同负债': he_tong_fu_zhai,
        '周转率': zhou_zhuan_tian,
        '扣非净利润增长率': li_run_lv
    })
    df.to_excel(result_path)